import pandas as pd
import numpy as np
from pandas import Series, DataFrame


### 四、pandas的拼接操作
def create_df(index=[], columns=[]):
    df = DataFrame({column: [column + str(i) for i in index] for column in columns})
    df.index = index
    return df


## 1 级联
df1 = create_df([1, 2, 3, 4], list('ABCD'))
df2 = create_df([1, 2, 3, 4], list('ABCD'))
# 1.1 简单级联
print(pd.concat([df1, df2]))
print(pd.concat([df1, df2], axis=1, ignore_index=True))
print(pd.concat([df1, df2], axis=1, keys=['df1', 'df2']))
# 1.2 不匹配级联
df3 = create_df([2, 3, 4, 5], list('BCDE'))
print(pd.concat([df1, df3]))
# 1.3 使用append函数
print(df1.append(df3))
## 2 合并
df1 = DataFrame({'name': ['张三', '李四', '王五'], 'id': [3, 2, 1], 'age': [21, 22, 23]})
df2 = DataFrame({'sex': ['男', '男', '女'], 'id': [2, 3, 4], 'group': ['销售', '管理', '服务']})
print(pd.merge(df1, df2, sort=True))
print(df1.merge(df2))
df3 = DataFrame({'name': ['张三', '李四', '张三'], 'salary': [10000, 12000, 20000], 'age': [22, 21, 25]})
df4 = DataFrame({'年龄': [21, 18, 29], 'id': ['张三', '张三', '凡凡'], 'group': ['销售', '管理', '服务']}, index=[22, 21, 25])
print(pd.merge(df3, df4, left_on='age', right_index=True))

# 补充：
s = Series(['Tom', 'Lucy', 'Tom', 'dancer', 'Lucy'])
print(s.unique())
n = DataFrame({'name': ['Tom', 'Lucy', 'Tom', 'dancer', 'Lucy'], 'age': [12, 13, 12, 11, 15]})
print(n)
a = 10
b = 2
# result1 result2 result3 等价
result1 = n.query('name=="Lucy" and age>@a+@b', inplace=True)
result2 = n.query('name=="Lucy" & age>@a+@b', inplace=True)
result3 = n[(n['name'] == 'Lucy') & (n['age'] > a + b)]


